package com.ningxia.water.mapper;

import com.ningxia.water.entity.TsApplicantInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.ApplicantAndWellsVo;
import com.ningxia.water.vo.out.ApplicantInfoVo;
import com.ningxia.water.vo.out.ReportVo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

/**
 *
 * @author hz
 * @since 2021-05-14
 */
@Mapper
public interface TsApplicantInfoMapper extends BaseMapper<TsApplicantInfo> {

    @Results(id = "applicantMap", value = {
            @Result(id = true, column = "applicant_id", property = "applicantId"),
            @Result(column = "applicant_name", property = "applicantName"),
//            @Result(column = "card_id",property = "cardId"),
//            @Result(column = "legal_person",property = "legalPerson"),
//            @Result(column = "live_address",property = "liveAddress"),
//            @Result(column = "code",property = "code"),
//            @Result(column = "product_address",property = "productAddress"),
//            @Result(column = "category",property = "category"),
//            @Result(column = "department",property = "department"),
//            @Result(column = "contactPerson",property = "contact_person"),
//            @Result(column = "phone",property = "phone"),
//            @Result(column = "co_applicant",property = "coApplicant"),
            @Result(column = "applicant_id", property = "wellNames",
                    many = @Many(
                            select = "com.ningxia.water.mapper.TsWellInfoMapper.findByApplicantId",
                            fetchType = FetchType.LAZY))
    })
    @Select("SELECT\n" +
            "  distinct ai.applicant_id,\n" +
            "\tai.applicant_name\n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id")
    List<ApplicantAndWellsVo> findAllWells();

    @Results(id = "applicantAndWarnWells", value = {
            @Result(id = true, column = "applicant_id", property = "applicantId"),
            @Result(column = "applicant_name", property = "applicantName"),
            @Result(column = "applicant_id", property = "wellNames",
                    many = @Many(
                            select = "com.ningxia.water.mapper.TsWellInfoMapper.findWarnWellByApplicantId",
                            fetchType = FetchType.LAZY))
    })
    @Select("SELECT\n" +
            "  distinct ai.applicant_id,\n" +
            "\tai.applicant_name\n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id")
    List<ApplicantAndWellsVo> findAllWarnWells();


    /**
     * 获取用水户信息 sql
     * @param applicantName 用水户名称
     * @return
     */
    @Select("<script>" +
            "SELECT \n" +
            "ai.applicant_id,\n" +
            "ai.applicant_name,\n" +
            "ai.card_id,\n" +
            "ai.legal_person,\n" +
            "ai.live_address,\n" +
            "ai.`code`,\n" +
            "ai.product_address,\n" +
            "ai.category,\n" +
            "td.department_id,\n" +
            "td.department_name,\n" +
            "ai.contact_person,\n" +
            "ai.phone,\n" +
            "ai.co_applicant,\n" +
            "IFNULL(zai.area_name, zti.town_name) as address_name ,ai.zone_id,ai.zone_type  " +
            "FROM \n" +
            "ts_applicant_info ai\n" +
            "LEFT JOIN zt_area_info zai ON ai.zone_id = zai.area_id and ai.zone_type = 1\n" +
            "LEFT JOIN zt_town_info zti ON ai.zone_id = zti.town_id and ai.zone_type = 2 " +
            "LEFT JOIN ts_department td ON ai.department_id = td.department_id " +
            "<where> " +
            "<if test='applicantName!=null'> " +
            "and ai.applicant_name like concat('%', #{applicantName}, '%')" +
            "</if>" +
            "<if test='zoneId!=null'> " +
            "and ai.zone_id = #{zoneId}" +
            "</if>" +
            "<if test='zoneType!=null'> " +
            "and ai.zone_type = #{zoneType}" +
            "</if>" +
            "</where>" +
            "</script>")
    List<ApplicantInfoVo> getApplicantInfo(String applicantName, Integer zoneId, Integer zoneType);

    /**
     * 用水户一天用水统计
     * @param currentTime
     * @return
     */
    @Select("SELECT " +
            "ai.applicant_id as id, " +
            "ai.applicant_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM ts_applicant_info ai\n" +
            "LEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "LEFT JOIN " +
            "ts_month_data md ON aw.well_id = md.well_id " +
            "AND md.time BETWEEN CONCAT(#{currentTime}, '-01') AND CONCAT(#{currentTime}, '-12')\n" +
            "GROUP BY ai.applicant_id " +
            "ORDER BY volume DESC")
    List<ReportVo> getApplicantStatisticalByYear(String currentTime);

    /**
     * 用水户一个月用水统计
     * @param currentTime
     * @return
     */
    @Select("SELECT " +
            "ai.applicant_id as id, " +
            "ai.applicant_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM ts_applicant_info ai\n" +
            "LEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "LEFT JOIN ts_month_data md ON aw.well_id = md.well_id AND md.time = #{currentTime}\n" +
            "GROUP BY ai.applicant_id \n" +
            "ORDER BY volume DESC")
    List<ReportVo> getApplicantStatisticalByMonth(String currentTime);


    /**
     * 获取用水户一年用水统计
     * @param currentTime
     * @return
     */
    @Select("SELECT " +
            "ai.applicant_id as id, " +
            "ai.applicant_name as name, " +
            "sum(dd.day_data)/10000 as volume\n" +
            "FROM ts_applicant_info ai\n" +
            "LEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "LEFT JOIN ts_day_data dd ON aw.well_id = dd.well_id AND dd.time = #{currentTime}\n" +
            "GROUP BY ai.applicant_id " +
            "ORDER BY volume DESC")
    List<ReportVo> getApplicantStatisticalByDay(String currentTime);
}
